package com.shujia.spark.sql

import org.apache.spark.sql.functions.expr
import org.apache.spark.sql.{Column, DataFrame, SparkSession}

object Demo8Student {
  def main(args: Array[String]): Unit = {


    val spark: SparkSession = SparkSession
      .builder()
      .master("local")
      .appName("burk")
      .config("spark.sql.shuffle.partitions", 1)
      .getOrCreate()
    import spark.implicits._
    import org.apache.spark.sql.functions._


    val itemDF: DataFrame = spark
      .read
      .format("csv")
      .option("sep", ",")
      .schema("name STRING,item STRING ,score DOUBLE")
      .load("data/item.txt")

    val df2: DataFrame = itemDF
      .groupBy($"name")
      .agg(
        sum(when($"item" === "数学", $"score").otherwise(0)) as "math",
        sum(when($"item" === "英语", $"score").otherwise(0)) as "english"
      )

    df2.show()

    val kv: Column = map(
      expr("'数学'"), $"math",
      expr("'英语'"), $"english"
    )

    df2
      .select($"name", explode(kv) as Array("item", "score"))
      .show()

  }

}
